import time

from sqlalchemy import Select, Column
from sqlmodel import SQLModel, Field, Session, select

import user
from settings import auto_session


class EmailCaptcha(SQLModel, table=True):
    __tablename__ = "emailcaptcha"
    id: int = Field(primary_key=True, default=None)
    email: str = Field(nullable=False, foreign_key="userinfo.email")
    captcha: str
    created_at: int = Field(nullable=False)
    expires_at: int
    used: bool = Field(default=False)

    def __init__(self, **kwargs):
        self.created_at = int(time.time())
        super().__init__(**kwargs)

    def to_dict(self) -> dict:
        return self.model_dump()
@auto_session
def search_captcha(email: str, session: Session = None) -> dict | None:
    """
    根据邮箱查询验证码
    """
    statement: Select = select(EmailCaptcha).where(
        EmailCaptcha.email == email, 
        EmailCaptcha.used == False
    ).order_by(EmailCaptcha.created_at.desc())  # 新增排序条件按创建时间降序排列
    
    emailcaptcha = session.exec(statement).first()  # 获取排序后的第一条（即最新记录）
    if emailcaptcha:
        return emailcaptcha.to_dict()
    else:
        return None

@auto_session
def insert_email_captcha(email: str, captcha: str, expires_at: int, session: Session = None) -> int | None:
    """
    插入一条验证码记录
    成功返回验证码id，失败则报错
    """
    emailcaptcha = EmailCaptcha(email=email, captcha=captcha, expires_at=expires_at)
    session.add(emailcaptcha)
    try:
        session.commit()
        return emailcaptcha.id
    except Exception as e:
        session.rollback()
        raise e

@auto_session
def set_a_captcha_as_used(email: str, session: Session = None) -> int | None:
    """
    设置某个验证码为已使用
    成功返回0，没有验证码返回-1
    """
    stmt = select(EmailCaptcha).where(EmailCaptcha.email == email, EmailCaptcha.used == False)
    emailcaptcha = session.exec(stmt).first()
    if emailcaptcha:
        emailcaptcha.used = True
        session.add(emailcaptcha)
        session.commit()
        return 0
    else:
        return -1